package com.apes.fn.server.productRequire;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.mvel2.util.Make;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface ProductRequireItemRepository extends CustomRepository<ProductRequireItem, String> {

    @Modifying
    @Query(value = "UPDATE STOCK_PRODUCT_REQUIRE_ITEM SET QTY_ASSIGNED = 0 WHERE PRODUCT_REQUIRE_ID = :id ", nativeQuery = true)
    void updateQtyArrive(@Param("id") String id);

    /** 线下要货仓库 */
    @Query(value = "" +
            "SELECT DISTINCT A.ID, C.F_LOCATION_ID, C.C_LOCATION_ID, A.LOCATION_ID, C.OFFLINE_WEIGHTS  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B, " +
            "       MAS_DC_DISTRIBUTION_RANGE C " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID AND A.ID = :id" +
            "   AND A.LOCATION_ID = C.C_LOCATION_ID " +
            "   AND (COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'WC%' OR COMMODITY_INQUIRY_ORDER_ID IS NULL) " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND (A.CHANNEL_ID NOT IN (:chanelId) OR A.CHANNEL_ID IS NULL) " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') " +
            " ORDER BY A.ID, C.OFFLINE_WEIGHTS DESC ", nativeQuery = true)
    List<Map> offlineProductRequireLocation(@Param("id") String id, @Param("chanelId") List chanelId); // 需加系统参数-------------------------


    @Query(value = "" +
            "SELECT DISTINCT A.ID, C.F_LOCATION_ID, C.C_LOCATION_ID, A.LOCATION_ID, C.OFFLINE_WEIGHTS  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B, " +
            "       MAS_DC_DISTRIBUTION_RANGE C " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = C.C_LOCATION_ID " +
            "   AND ((COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'WC%' AND B.COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'ST%') OR COMMODITY_INQUIRY_ORDER_ID IS NULL) " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND (A.CHANNEL_ID NOT IN (:chanelId) OR A.CHANNEL_ID IS NULL) " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') " +
            " ORDER BY A.ID, C.OFFLINE_WEIGHTS DESC ", nativeQuery = true)
    List<Map> offlineProductRequireLocation(@Param("chanelId") List chanelId);


    /** 线下缺货要货明细 */
    @Query(value = "" +
            "SELECT A.ID, A.LOCATION_ID, B.PRODUCT_ID, B.ID REQUIRE_ITEM_ID, B.PRODUCT_UOM_ID, " +
            "       (B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY) RABBET " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.ID = :id " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') AND (COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'WC%' OR COMMODITY_INQUIRY_ORDER_ID IS NULL) ", nativeQuery = true)
    List<Map> offProductRequireItem(@Param("id") String id);



    /** 线上要货仓库 */
    @Query(value = "" +
            "SELECT DISTINCT C.F_LOCATION_ID, C.C_LOCATION_ID, A.LOCATION_ID, C.OFFLINE_WEIGHTS, C.ONLINE_WEIGHTS  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B, " +
            "       MAS_DC_DISTRIBUTION_RANGE C " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = C.C_LOCATION_ID " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') " +
            " ORDER BY C.ONLINE_WEIGHTS DESC ", nativeQuery = true)
    List<Map> onlineProductRequireLineLocation(); // 需加系统参数-------------------------

    /** 线上缺货要货明细汇总 */
    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.PRODUCT_UOM_ID, SUM(RABBET) RABBET\n" +
            "  FROM (SELECT A.LOCATION_ID, B.PRODUCT_ID, B.PRODUCT_UOM_ID, " +
            "       (B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY) RABBET " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = :locationId " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND A.CHANNEL_ID IN (:systemValue) " +
            "   AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') AND (COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'WC%' OR COMMODITY_INQUIRY_ORDER_ID IS NULL) " +
            " ORDER BY APPROVE_DATE DESC ) A\n" +
            "GROUP BY A.LOCATION_ID, A.PRODUCT_ID, A.PRODUCT_UOM_ID", nativeQuery = true)
    List<Map> onLineProductRequireItemSum(@Param("locationId") String locationId, @Param("systemValue") List systemValue); // 需加系统参数-------------------------


    /** 线上缺货要货明细 */
    @Query(value = "" +
            "SELECT A.ID, B.ID ITEM_ID, A.LOCATION_ID, B.PRODUCT_ID, B.PRODUCT_UOM_ID, B.QTY_REQUIRE, A.INNER_BUSINESS_TYPE, " +
            "       (B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY) RABBET  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B  " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = :locationId AND B.PRODUCT_ID = :productId " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND A.CHANNEL_ID IN (:systemValue) " +
            "   AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND STATE IN ('approve', 'repeal') " +
            " ORDER BY APPROVE_DATE DESC ", nativeQuery = true)
    List<Map> onLineProductRequireItem(@Param("locationId") String locationId, @Param("productId") String productId, @Param("systemValue") List systemValue); // 需加系统参数-------------------------


    /** 线上要货 - 要货补货管理全部补货的情况 */
    @Query(value = "" +
            "SELECT A.ID, B.ID ITEM_ID, A.LOCATION_ID, B.PRODUCT_ID, B.PRODUCT_UOM_ID, B.QTY_REQUIRE, A.INNER_BUSINESS_TYPE, " +
            "       (B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY) RABBET  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B  " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = :locationId AND B.PRODUCT_ID = :productId " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND STATE IN ('approve', 'repeal') " +
            " ORDER BY APPROVE_DATE DESC ", nativeQuery = true)
    List<Map> onLineProductRequireItem(@Param("locationId") String locationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT DISTINCT A.ID " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.CHANNEL_ID IN (:systemValue) " +
            "   AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID AND USAGE_ID = '1') " +
            "   AND A.STATE IN ('approve', 'repeal') AND (COMMODITY_INQUIRY_ORDER_ID NOT LIKE 'WC%' OR COMMODITY_INQUIRY_ORDER_ID IS NULL) ", nativeQuery = true)
    List<Map> onLineRequireSynOldFrame(@Param("systemValue") List systemValue);


    /** 可用库存 */
    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStock(@Param("flocationId") String flocationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH WHERE SUPPLIER_ID IN (:supplierIds) GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockSupplier(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List supplierIds);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH WHERE SUPPLIER_ID IN (:supplierIds) GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map validStock(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List<PartyRole> supplierIds);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH L WHERE EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND ID = L.SUPPLIER_ID) GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockAllSupplier(@Param("flocationId") String flocationId, @Param("productId") String productId);

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH L " +
            "          WHERE EXISTS (SELECT 1 FROM MAS_PARTY_ROLE T WHERE ROLE_ID = 'Supplier' AND ID = L.SUPPLIER_ID) " +
            "            AND EXISTS (SELECT 1 FROM ACC_CONTRACT WHERE STATE IN ('approve', 'extension') AND VALID_PERIOD >= GET_DATE() AND CONTRACT_TYPE = 'Contract.contractType.supplier' AND PARTNER_ID = L.SUPPLIER_ID) " +
            "         GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockAllSupplierContract(@Param("flocationId") String flocationId, @Param("productId") String productId); // 直营转赋能特殊控制, 必须要有合同才能转

    @Query(value = "" +
            "SELECT A.LOCATION_ID, A.PRODUCT_ID, A.COMPANY_ID, (SELECT ID FROM MAS_PRODUCT_UOM WHERE UOM_CATEGORY_ID  = A.PRODUCT_ID AND FACTOR = 1 and VALID = 1 and rownum = 1) PRODUCT_UOM_ID, " +
            "       CASE WHEN A.USABLE_QTY > B.BALANCE_QTY THEN B.BALANCE_QTY ELSE A.USABLE_QTY END USABLE_QTY " +
            "  FROM ACC_STOCK A, " +
            "       (SELECT SUM(BALANCE_QTY - FROST_QTY) BALANCE_QTY, COMPANY_ID, PRODUCT_ID FROM ACC_INVENTORY_BATCH J " +
            "          WHERE SUPPLIER_ID IN (:supplierIds) " +
            "            AND EXISTS (SELECT 1 FROM ACC_CONTRACT WHERE STATE IN ('approve', 'extension') AND VALID_PERIOD >= GET_DATE() AND CONTRACT_TYPE = 'Contract.contractType.supplier' AND PARTNER_ID = J.SUPPLIER_ID) " +
            "        GROUP BY COMPANY_ID, PRODUCT_ID) B " +
            " WHERE A.COMPANY_ID = B.COMPANY_ID " +
            "   AND A.PRODUCT_ID = B.PRODUCT_ID " +
            "   AND USABLE_QTY > 0 AND BALANCE_QTY > 0 " +
            "   AND A.LOCATION_ID = :flocationId AND A.PRODUCT_ID = :productId ", nativeQuery = true)
    Map availableStockSupplierContract(@Param("flocationId") String flocationId, @Param("productId") String productId, @Param("supplierIds") List supplierIds); // 作用同上 availableStockAllSupplierContract

    @Query(value = "" +
            "SELECT PRODUCT_REQUIRE_ID, " +
            "       (SELECT STATE FROM STOCK_PRODUCT_REQUIRE WHERE ID = A.PRODUCT_REQUIRE_ID) STATE " +
            "  FROM STOCK_PRODUCT_REQUIRE_ITEM A " +
            " WHERE A.QTY_REQUIRE - A.QTY_ENDED - A.QTY_ARRIVE > 0 " +
            "   AND EXISTS (SELECT 1 FROM STOCK_PRODUCT_REQUIRE_ITEM " +
            "         WHERE PRODUCT_REQUIRE_ID = A.PRODUCT_REQUIRE_ID AND ID = :requireItemId) ", nativeQuery = true)
    List<Map> judgeExistsRequire(@Param("requireItemId")String requireItemId);

    @Query(value = "" +
            "SELECT T FROM ProductRequireItem T " +
            " WHERE productRequire.id IN (:requireIds) AND productRequire.location.id = :locationId AND product.id = :productId " +
            "   AND qtyRequire - qtyAssigned - qtyEnded - orderQty > 0 ", nativeQuery = true)
    List<ProductRequireItem> qryGapRequireItem(@Param("requireIds") List requireIds, @Param("locationId") String locationId, @Param("productId") String productId);





    /** 要货补货管理要货转储 */
    @Query(value = "" +
            "SELECT A.ID, B.ID ITEM_ID, A.LOCATION_ID, B.PRODUCT_ID, B.PRODUCT_UOM_ID, B.QTY_REQUIRE, A.INNER_BUSINESS_TYPE, " +
            "       (B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY) RABBET  " +
            "  FROM STOCK_PRODUCT_REQUIRE A, STOCK_PRODUCT_REQUIRE_ITEM B  " +
            " WHERE A.ID = B.PRODUCT_REQUIRE_ID " +
            "   AND A.LOCATION_ID = :locationId AND B.PRODUCT_ID = :productId " +
            "   AND B.QTY_REQUIRE - B.QTY_ASSIGNED - B.QTY_ENDED - B.ORDER_QTY > 0 " +
            "   AND A.CREATE_DATE >= SYSDATE - 180 " +
            "   AND EXISTS (SELECT 1 FROM MAS_LOCATION WHERE ID = A.LOCATION_ID) " +
            "   AND STATE IN ('approve', 'repeal') " +
            "   AND A.ID IN (:requireIds) " +
            " ORDER BY APPROVE_DATE DESC ", nativeQuery = true)
    List<Map> replenishmentManageRequireItem(@Param("locationId") String locationId, @Param("productId") String productId, @Param("requireIds") List requireIds);

    @Query(value = "SELECT A.* FROM STOCK_PRODUCT_REQUIRE_ITEM A " +
            "  WHERE product_require_id = :productRequireId ", nativeQuery = true) // AND qty_require - qty_assigned - qty_ended - order_qty > 0
    List<ProductRequireItem> qryCanCancelRequire(@Param("productRequireId") String productRequireId);


    @Query(value = "SELECT * FROM STOCK_PRODUCT_REQUIRE_ITEM WHERE SUPPLIER_ID = ?1 AND PRODUCT_REQUIRE_ID = ?2 ", nativeQuery = true)
    List<ProductRequireItem> findBySupplierIdAndProductRequireId(String supplierId, String productRequireId);








}






















